<?php

/**
 * Get latest Product
 */
$query = "
	SELECT MAX(`id`) as last , `label`, `id`
	FROM `product`
	WHERE `status`='enable'
	GROUP BY `name`
	ORDER BY last
";

$result = $dbMysql->query($query);
while ($row = mysql_fetch_assoc($result)){
	$listLastVersion[] = $row['last'];
	$lblLastVersion[$row['id']] = $row['label'];
}

/**
 * Get the authorized language for each product
 */
$query = "
	SELECT * 
	FROM `product_preferences` 
	WHERE `product_id` IN (".implode(',',$listLastVersion).")
";

$mapLangProduct = fetchArray($query, 'product_id' , true);

/**
 * Get count traduction by Project/language
 */
$query ="
	SELECT p.*, count(t.`id`) as count, pp.`project_id`, t.`language_id`
	FROM `translation` t
	JOIN `property_key` as pk ON pk.`id` = t.`property_key_id`
	JOIN `file` as f ON f.`id` = pk.`file_id`
	JOIN `product_project` as pp ON pp.`project_id`=f.`project_id`	
	JOIN `project` as p ON p.`id`=pp.`project_id`
	JOIN `language` as l ON l.`id`=t.`language_id`
	WHERE 
		p.`status` = 'enable' AND 
		pp.`product_id` IN (".implode(',',$listLastVersion).") AND
		t.`status` IN ('translated','validated')
	GROUP BY p.`id`, t.`language_id`
	ORDER BY p.`id`, t.`language_id`
";

$statByProject = fetchArray($query, 'project_id' , true);

/**
 * Make array stats (product=>project=>language = count)
 */
$array_stats=array();
foreach($listLastVersion as $product){
	$count_all=0;
	if(isset($statByProject[$product])){
		foreach($statByProject[$product] as $project){
			$array_stats[$product]['all']+= intval($project['count']);
			$array_stats[$product][$project['language_id']] += intval($project['count']);
		}
	}
}

/**
 * Output / html
 */
echo '
<div align="center" class="home_progress">
	<table align="center">';
	
	foreach($lblLastVersion as $key => $value){
		echo '
		<tr>
		  <td>
			<table align="center">
			 <tr>
				<td>
					<table width="100%"><tr><td class="name_project" valign="top">'. $value.'&nbsp;</td><td align="left" class="complete_at">Complete at:&nbsp;</td></tr></table>
				</td>
			 </tr>
		';
		
		foreach($mapLangProduct[$key] as $lang){
			$html='<table id="progressBar" background="/babili/img/bg_progress.jpg" cellspacing="0" cellpadding="0" border="0" width="360" ><tr>'."\n";
			
			$count = $array_stats[$key][$lang['language_id']];
			
			(isset($array_stats[$key]['all']))?	$count_all = $array_stats[$key]['all']: $count_all=0;
			
			($count_all>0)?	$pourcent=ceil($count/$count_all*100) : $pourcent=100;
			
			($count_all==$count)?$class_progress="green" : $class_progress="blue";
			
			$width=ceil(270*$pourcent/100);
			$width_right=270-$width;
								
			$html .="<td class='".$class_progress."left' nowrap='nowrap' width='3'></td>\n";
			$html .="<td class='".$class_progress."' nowrap='nowrap' width='".$width."' height='16'></td>\n";
			$html .="<td nowrap='nowrap' height='16' class='progress_end_".$class_progress."' width='3'>&nbsp;</td>\n";
			$html .='<td class="progress_right"  nowrap="nowrap" width="'.$width_right.'"></td>';
			$html .='<td nowrap="nowrap" class="flag" valign="middle" width="16"><img src="'.$resultLangues[$lang['language_id']]['flag'].'" alt="'.$resultLangues[$lang['language_id']]['country_code'].'"/></td>';
			$html .='<td class="pourcentage" valign="middle" align="left" width="30">'.$pourcent.'% </td>';
			$html .='</tr></table>';
			
			echo '
			<tr>
			 	<td align="left">'.
					$html.'
				</td>
			</tr>
			';
		}	
		echo '</table>
		  </td>
		</tr>';
	}
	
echo '
  	  </table>
	 </td>
    </tr>
  </table>
  <p>&nbsp;</p>
</div>
';
?>